using System;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data.SQLite;
using System.Data;
using sharpCash.ReflectionClasses;
using System.Windows.Forms;

namespace sharpCash.SQLwrapper
{
    /// <summary>
    /// The entire purpose of this class is to run the basic SQL functions.
    /// </summary>
    public class BaseSQL
    {
        protected SQLiteConnection sqlCon;
        protected SQLiteCommand sqlCmd;
        /// <summary>
        /// Open the SQL file and get it ready to use.
        /// </summary>
        /// <param name="fileName">File of the SQL data...usually :memory:</param>
        public BaseSQL(string fileName)
        {
            try
            {
                sqlCon = new SQLiteConnection("Data Source=" + fileName + "");
                sqlCon.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Can not load " + fileName + ": " + ex.Message);
            }
            if (sqlCon == null || sqlCon.State != System.Data.ConnectionState.Open)
            {
                sqlCon = null;
            }
        }
        /// <summary>
        /// As the function says, add an unknown object to the SQL file.
        /// </summary>
        /// <param name="obj">The unknown object to store into the SQL file</param>
        /// <returns>If the storage was successful.</returns>
        public virtual bool addDataToDB(object obj)
        {
            bool rv = false;
            string sqlText = "";
            try
            {
                sqlText = "INSERT INTO " + obj.GetType().Name + " VALUES(" + ObjectStringClass.objectValues(obj) + ",NULL)";
                sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                int rows = sqlCmd.ExecuteNonQuery();
                rv = true;
                sqlCmd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Crap, I have a problem.  Can you send this to the bug report: " + ex.Message + " and this too " + sqlText);
            }
            return rv;
        }
        /// <summary>
        /// This function returns the best match to the object given to it
        /// </summary>
        /// <param name="obj">An unknown object of a type that is stored in the SQL tables</param>
        /// <returns>The best match of the SQL table</returns>
        public virtual BestMatchFunction BestMatch(object obj)
        {
            string names = ObjectStringClass.objectMemberNames(obj);
            names = names.Replace('|', ',');
            names += ",ID";
            string sqlText = "SELECT BestMatch(" + names + ") as ret from " + obj.GetType().Name + " ORDER BY ret+0";
            this.sqlCmd = new SQLiteCommand("", this.sqlCon);
            this.sqlCmd.CommandText = sqlText;
            BestMatchFunction myFunct = new BestMatchFunction(names, obj);
            SQLiteDataReader myReader = this.sqlCmd.ExecuteReader();
            myReader.Dispose();
            this.sqlCmd.Dispose();
            return myFunct;
        }
        /// <summary>
        /// Removed a item from the SQL table given the ID and object type
        /// </summary>
        /// <param name="obj">Doesnt matter if the object is populated...it just need to be the right type</param>
        /// <param name="ID">The ID of the item to remove</param>
        /// <returns>If it succeeds or fails.</returns>
        public virtual bool removeDataInDB(object obj, object ID)
        {
            bool rv = false;
            string sqlText = "DELETE FROM " + obj.GetType().Name + " WHERE rowid=" + ID.ToString();
            try
            {
                sqlCmd = new SQLiteCommand(sqlText, sqlCon);
                int rows = sqlCmd.ExecuteNonQuery();
                rv = true;
                sqlCmd.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Crap, I have a problem.  Can you send this to the bug report: " + ex.Message + " and this too " + sqlText);
            }
            return rv;

        }
        /// <summary>
        /// This one updates the table with the ID given to the data in the object given
        /// </summary>
        /// <param name="obj">Object to store into the SQL table</param>
        /// <param name="ID">The ID of the object that is already in the table.</param>
        /// <returns>If it was successful</returns>
        public virtual bool addDataToDB(object obj, object ID)
        {
            string sqlText = "UPDATE " + obj.GetType().Name + " SET " + ObjectStringClass.objectValues(obj, ",", true) + " WHERE rowid=" + ID.ToString();
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            int rows = sqlCmd.ExecuteNonQuery();
            sqlCmd.Dispose();
            return true;
        }
        /// <summary>
        /// Return an object based off of another object.  Find every month and make a data set for each month, that holds the objects
        /// that have that month.  Makes Since?
        /// </summary>
        /// <param name="obj">Object of type to look into the right table</param>
        /// <param name="whereClause">What parameters to limit by</param>
        /// <param name="orderByClause">How to order the data</param>
        /// <param name="colNames">Column names to return</param>
        /// <param name="column">Column to do initial search for.</param>
        /// <returns>A Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnMulti(object obj, object whereClause, object orderByClause, object colNames, string column)
        {
            string sqlText;
            DataSet ds = new DataSet();
            DataSet rv = new DataSet();
            SQLiteDataAdapter sqlAdapter;
            string objectName = obj.GetType().Name;
            int i = 0;
            sqlText = "SELECT DISTINCT(" + column + ") FROM " + objectName;
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            sqlAdapter = new SQLiteDataAdapter(sqlCmd);
            sqlAdapter.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                DataSet tmpDS = new DataSet();
                object tmpObj = dr[column];
                sqlText = "SELECT ";
                if (colNames != null && colNames.ToString() != "")
                    sqlText += colNames.ToString();
                else
                    sqlText += "*";
                sqlText += " FROM " + objectName;
                if (whereClause != null && whereClause.ToString() != "")
                {
                    sqlText += " WHERE " + whereClause.ToString();
                    i = 1;
                }
                if (i == 1)
                    sqlText += " AND ";
                if (tmpObj != null)
                    sqlText += column + "='" + tmpObj.ToString() + "'";
                else sqlText += column + "=NULL";
                if (orderByClause != null && orderByClause.ToString() != "")
                    sqlText += " ORDER BY " + orderByClause.ToString();
                SQLiteCommand sqlCmd2 = new SQLiteCommand(sqlText, sqlCon);
                SQLiteDataAdapter sqlAdapter2 = new SQLiteDataAdapter(sqlCmd2);
                sqlAdapter2.Fill(tmpDS);
                tmpDS.Tables[0].TableName = dr[column].ToString();
                rv.Tables.Add(tmpDS.Tables[0].Copy());
            }
            sqlCmd.Dispose();
            sqlAdapter.Dispose();
            return rv;
        }
        /// <summary>
        /// Return all objects of the given object type
        /// </summary>
        /// <param name="obj">Object of type to return</param>
        /// <returns>Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnAll(object obj)
        {
            return this.returnAll(obj, null);
        }
        /// <summary>
        /// Return all objects of the given object type
        /// </summary>
        /// <param name="obj">Object of type to return</param>
        /// <param name="whereClause">What to limit the search by</param>
        /// <returns>Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnAll(object obj, string whereClause)
        {
            return this.returnAll(obj, whereClause, null);
        }
        /// <summary>
        /// Return all objects of the given object type
        /// </summary>
        /// <param name="obj">Object of type to return</param>
        /// <param name="whereClause">What to limit the search by</param>
        /// <param name="orderByClause">How to order the data</param>
        /// <returns>Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnAll(object obj, string whereClause, string orderByClause)
        {
            return this.returnAll(obj, whereClause, orderByClause, null);
        }
        /// <summary>
        /// Return all objects of the given object type
        /// </summary>
        /// <param name="obj">Object of type to return</param>
        /// <param name="whereClause">What to limit the search by</param>
        /// <param name="orderByClause">How to order the data</param>
        /// <param name="colNames">Columns to return</param>
        /// <returns>Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnAll(object obj, string whereClause, string orderByClause, string colNames)
        {
            DataSet rv = new DataSet();
            sqlCmd = new SQLiteCommand(this.makeSQLQuery(obj, whereClause, orderByClause, colNames), sqlCon);
            SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqlCmd);
            sqlAdapter.Fill(rv);
            sqlCmd.Dispose();
            sqlAdapter.Dispose();
            return rv;
        }
        /// <summary>
        /// Make a text SQL query.
        /// </summary>
        /// <param name="obj">Object type</param>
        /// <param name="whereClause">Limiting clause.  String Data</param>
        /// <param name="orderByClause">What to order by.  String Data</param>
        /// <param name="colNames">What to return.  String Data</param>
        /// <returns>The SQL string</returns>
        public virtual string makeSQLQuery(object obj, object whereClause, object orderByClause, object colNames)
        {
            string sqlText;
            sqlText = "SELECT ";
            if (colNames != null && colNames.ToString() != "")
                sqlText += colNames.ToString();
            else
                sqlText += "*";
            sqlText += " FROM " + obj.GetType().Name;
            if (whereClause != null && whereClause.ToString() != "")
                sqlText += " WHERE " + whereClause.ToString();
            if (orderByClause != null && orderByClause.ToString() != "")
                sqlText += " ORDER BY " + orderByClause.ToString();
            return sqlText;
        }
        /// <summary>
        /// Ok, this makes an SQL hash table that holds all the strings to be put into makeSQLQuery
        /// </summary>
        /// <param name="obj">Object type</param>
        /// <param name="whereClause">Limiting clause.  String Data</param>
        /// <param name="orderByClause">What to order by.  String Data</param>
        /// <param name="colNames">What to return.  String Data</param>
        /// <returns>Hashtable of the strings</returns>
        public virtual Hashtable makeSQLobject(object obj, string whereClause, string orderByClause, string colNames)
        {
            Hashtable rv = new Hashtable();
            rv.Add("TABLE", obj);
            rv.Add("WHERE", whereClause);
            rv.Add("ORDER BY", orderByClause);
            rv.Add("COLUMNS", colNames);
            return rv;
        }
        /// <summary>
        /// Return data using a custom SQL string
        /// </summary>
        /// <param name="selectSTMT">custom SQL string</param>
        /// <returns>Dataset of the SQL data you asked for</returns>
        public virtual DataSet returnCustom(string selectSTMT)
        {
            DataSet rv = new DataSet();
            sqlCmd = new SQLiteCommand(selectSTMT, sqlCon);
            SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqlCmd);
            sqlAdapter.Fill(rv);
            sqlCmd.Dispose();
            sqlAdapter.Dispose();
            return rv;
        }
        /// <summary>
        /// Find the last object id that was entered
        /// </summary>
        /// <param name="obj">object of type to look at</param>
        /// <returns>The string version of the ID</returns>
        public virtual string findLast(object obj)
        {
            string rv = "0";
            SQLiteCommand sqlCmd2 = new SQLiteCommand("", sqlCon);
            string sqlText = "SELECT coalesce(max(ID),0)+1 from " + obj.GetType().Name;
            sqlCmd2.CommandText = sqlText;
            SQLiteDataReader sqlReader2 = sqlCmd2.ExecuteReader();
            while (sqlReader2.Read())
            {
                rv = sqlReader2.GetValue(0).ToString();
            }
            sqlCmd2.Dispose();
            sqlReader2.Dispose();
            return rv;
        }
        /// <summary>
        /// If the table doesnt exist, make it.
        /// </summary>
        /// <param name="obj">Object of type to make table.</param>
        public virtual void makeTableIfExist(object obj)
        {
            if (this.sqlCon != null)
            {
                string holder = ObjectStringClass.objectMemberNames(obj);
                string varibles = string.Join(" TEXT, ", holder.Split('|'));
                varibles += " TEXT, ID INTEGER PRIMARY KEY AUTOINCREMENT";
                string sqlText = "CREATE TABLE IF NOT EXISTS " + obj.GetType().Name + " (" + varibles + ")";
                this.sqlCmd = new SQLiteCommand(sqlText, this.sqlCon);
                this.sqlCmd.CommandText = sqlText;
                this.sqlCmd.ExecuteNonQuery();
                sqlCmd.Dispose();
            }
        }
        /// <summary>
        /// If the object you gave to me exists, I will find it.
        /// </summary>
        /// <param name="obj">The object to find</param>
        /// <returns>If it exist, I will return the data set</returns>
        public virtual DataSet findIfObjExists(object obj)
        {
            DataSet rv = new DataSet();
            string sqlText = "SELECT * FROM " + obj.GetType().Name + " WHERE " + ObjectStringClass.objectValues(obj, " AND ", true, true);
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            SQLiteDataAdapter sqlAdapter = new SQLiteDataAdapter(sqlCmd);
            sqlAdapter.Fill(rv);
            sqlCmd.Dispose();
            sqlAdapter.Dispose();
            return rv;
        }
        /// <summary>
        /// Begin a Transaction
        /// </summary>
        /// <param name="obj">Doesnt really matter, just keep it consistant with endTransaction</param>
        public virtual void beginTransaction(object obj)
        {
            string sqlText = "BEGIN TRANSACTION " + obj.GetType().Name;
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Dispose();
        }
        /// <summary>
        /// End a Transaction
        /// </summary>
        /// <param name="obj">Doesnt really matter, just keep it consistant with beginTransaction</param>
        public virtual void endTransaction(object obj)
        {
            string sqlText = "END TRANSACTION " + obj.GetType().Name;
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            sqlCmd.ExecuteNonQuery();
            sqlCmd.Dispose();
        }
        /// <summary>
        /// Return the ID of the one object that you are looking for.  Also, when you find an object
        /// using the where clause, store it into the object given.
        /// </summary>
        /// <param name="obj">An object to overwrite with the where clause given</param>
        /// <param name="whereClause">What to search for.</param>
        /// <param name="orderByClause">How to order the data (Since you really only get the first object)</param>
        /// <param name="colNames">Column names to return</param>
        /// <returns>ID of the object you are returning.</returns>
        public virtual string returnOneObject(object obj, object whereClause, object orderByClause, object colNames)
        {
            string sqlText = this.makeSQLQuery(obj, whereClause, orderByClause, colNames);
            sqlCmd = new SQLiteCommand(sqlText, sqlCon);
            SQLiteDataReader dr = sqlCmd.ExecuteReader();
            object rv;
            string ID = "";
            rv = obj.GetType().GetConstructor(Type.EmptyTypes).Invoke(null);
            while (dr.Read())
            {
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    if (dr.GetName(i) == "ID")
                        ID = dr.GetValue(i).ToString();
                    ObjectStringClass.setObjectValue(obj, dr.GetValue(i), dr.GetName(i));
                }
                break;
            }
            dr.Dispose();
            sqlCmd.Dispose();
            return ID;
        }
    }
    /// <summary>
    /// Now this one is a bit confusing and a PAIN, but when it works, it is LOVELY.
    /// It try to make the best match between one object and a SQL table.
    /// </summary>
    [SQLiteFunction(Arguments = -1, FuncType = FunctionType.Scalar, Name = "BestMatch")]
    public class BestMatchFunction : SQLiteFunction
    {
        static private string[] argColNames;
        static private object objToCompare;
        static private Hashtable bestMatches;
        static private int numCols;
        static private int bestMatchNumber;
        static private Hashtable myObjectNames;
        static private int maxMatches;
        static private Hashtable attrHash;

        public BestMatchFunction()
        {
        }
        public object getBestMatch()
        {
            return this.getBestMatch(0.50f);
        }
        public object getBestMatch(float percentage)
        {
            object rv = null;
            float matchRatio = ((float)bestMatchNumber / (float)maxMatches);
            if (matchRatio > percentage)
            {
                ArrayList myList = (ArrayList)bestMatches[bestMatchNumber];
                rv = myList.Count == 1 ? myList[0] : null;
            }
            return rv;
        }
        /// <summary>
        /// Get the columns sent to the SQL query and get the object to compare it to.
        /// </summary>
        /// <param name="colNames">The column names</param>
        /// <param name="compare">The object to compare it all too.</param>
        public BestMatchFunction(string colNames, object compare)
            : base()
        {
            objToCompare = compare;
            argColNames = colNames.Split(',');
            numCols = argColNames.Length;
            bestMatches = new Hashtable();
            myObjectNames = new Hashtable();
            for (int i = 0; i <= numCols; i++)
            {
                bestMatches.Add(i, new ArrayList());
            }
            bestMatchNumber = 0;
            maxMatches = 0;
            string valueString = ObjectStringClass.objectValues(compare, ",", true);
            //The Regular expression splits the return value from the reflection code above.
            Regex stringSplit = new Regex(@"(((\w+=NULL)|(\w+='[^']*'))[,])|(((\w+=NULL)|(\w+='[^']*')))");
            MatchCollection myMatches = stringSplit.Matches(valueString);
            foreach (Match item in myMatches)
            {
                string[] myVar = item.Value.Split('=');
                string varName = myVar[0];
                string varVal = "";
                for (int i = 1; i < myVar.Length; i++)
                    varVal += myVar[i];
                if (varVal[varVal.Length - 1] == ',')
                    varVal = varVal.Remove(varVal.Length - 1);
                if (varVal[varVal.Length - 1] == '\'')
                    varVal = varVal.Remove(varVal.Length - 1);
                if (varVal[0] == '\'')
                    varVal = varVal.Remove(0, 1);
                if (ObjectStringClass.isDefaultValue(compare, varName) == 0)
                {
                    maxMatches++;
                    myObjectNames.Add(varName, varVal);
                }
                else myObjectNames.Add(varName, null);
            }
            attrHash = ObjectStringClass.getColumnAttributes(objToCompare);
        }
        /// <summary>
        /// The SQL code invokes this function to run the compare against the object given in constructor
        /// </summary>
        /// <param name="args">This is given by the SQLite code, it is the actual row from sqlite.</param>
        /// <returns>This returns the number of matches it makes</returns>
        public override object Invoke(object[] args)
        {
            int i = 0;
            string ID = "";
            ArrayList matches;
            for (int j = 0; j < args.Length; j++)
            {
                string myArg = args[j].ToString();
                if (argColNames[j] != "ID")
                {
                    if (myArg != "" && myObjectNames[argColNames[j]] != null)
                    {
                        string columnName = argColNames[j];
                        object attrObj = attrHash[columnName];
                        if (attrObj != null)
                        {
                            ColumnAttribute myAttr = (ColumnAttribute)attrObj;
                            Type mySortType = myAttr.SortType();
                            if (mySortType == typeof(DateTime))
                            {
                                int compare = DateTime.Compare(DateTime.Parse(myArg), DateTime.Parse(myObjectNames[columnName].ToString()));
                                if (compare == 0)
                                    i++;
                            }
                            else if (myArg == myObjectNames[argColNames[j]].ToString())
                            {
                                i++;
                            }
                        }
                    }
                }
                else ID = myArg;
            }
            if (i > bestMatchNumber)
                bestMatchNumber = i;
            matches = (ArrayList)bestMatches[i];
            if (matches.Count == 1 && matches[0].ToString() != ID)
                matches.Add(ID);
            else if (matches.Count == 0 && ID != "")
                matches.Add(ID);
            return i;
        }
    }
}
